import json
from datetime import datetime

import pymysql
import requests
from urllib.parse import urljoin

#11船舶污染物的接收和处理情况备案数量：一网通管-查询统计-危防业务统计-船舶污染物的接收和处理情况备案  68条
BASE_URL11 = 'http://198.10.76.67/dangerextend/dangerousBusinessExtend/getOtherList?size=10&current=1&affairId=92315CF3A3A8F140E053140F0AC66560&affairNum=&affairStatus=&applyNum=&shipName=&projectName=&userName=&applyStartTime=2024-10-01+00:00&applyEndTime=2024-12-31+23:59&acceptDepts=&acceptStartTime=&acceptEndTime=&approvalDepts=&approvalStartTime=&approvalEndTime=&approveOrgIsCheck=1&mechanismIsCheck=1'
GET_INFO_URL = 'http://198.10.76.67/manage/windowapplytwo/getById1/'

#请求头部
headers = {
    'User-Agent': 'Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/133.0.0.0 Safari/537.36 Edg/133.0.0.0',
    'Accept': 'application/json, text/plain, */*',
    'Authorization': 'Bearer 4d8f42a8-4d7e-4e65-9f15-bd5b0a7d44d8',
    'content-type': 'application/json;charset=UTF-8'
}
#账号身份token
cookies = {
    'access_token': 'eyJhbGciOiJIUzI1NiJ9.eyJpYXQiOjE3NDA5MDc0MjcsImV4cCI6MTc0MDkzNjIyNywic3ViIjoie1xuICBcInVzZXJJZFwiIDogXCIzNzNcIixcbiAgXCJwYXNzd29yZFwiIDogXCIkMmEkMTAkQ2lvcHkxbVBRTFRaOW5TSUpRY0ZaLnlnLjZRQzIvQXdMd29rVDJKY2NEVmtqSGJJbjdQZlNcIixcbiAgXCJ1c2VyQ29kZVwiIDogXCJsaXl1Y2hlbmdcIixcbiAgXCJ1c2VyTmFtZUNuXCIgOiBcIuadjueOieaIkFwiLFxuICBcImNsaWVudElwXCIgOiBcIlwiLFxuICBcImFwcElkXCIgOiBcIlwiLFxuICBcImFwcENvZGVcIiA6IFwiQ1RUSUNcIixcbiAgXCJvZmZpY2VJZFwiIDogXCIxNFwiLFxuICBcInN5c1JvbGVJZFwiIDogbnVsbCxcbiAgXCJvZmZpY2VOYW1lXCIgOiBcIua3seWcs-a1t-S6i-WxgFwiLFxuICBcInJvbGVJZHNcIiA6IFsgXCIyNDVcIiwgXCIxNjdcIiwgXCIxMTZcIiwgXCIxMTNcIiwgXCI2MzVcIiwgXCI5MFwiLCBcIjg4XCIsIFwiMTEyXCIsIFwiMjE1XCIsIFwiMjgzXCIsIFwiNjM0XCIsIFwiMjQ2XCIsIFwiNjY0XCIsIFwiNjYyXCIsIFwiNjY3XCIsIFwiMTE1XCIsIFwiMTcyXCIsIFwiNjQxXCIsIFwiNjI0XCIgXSxcbiAgXCJwYXNzd29yZE1vZGlmeVRpbWVcIiA6IG51bGwsXG4gIFwiaXNQYXNzd29yZE1vZGlmeVwiIDogbnVsbCxcbiAgXCJ1c2VybmFtZVwiIDogXCJsaXl1Y2hlbmdcIixcbiAgXCJkZXB0T2ZmaWNlQ29kZVwiIDogbnVsbCxcbiAgXCJkZXB0T2ZmaWNlTmFtZVwiIDogbnVsbCxcbiAgXCJzZXR0bGVPZmZpY2VDb2RlXCIgOiBudWxsLFxuICBcInNldHRsZU9mZmljZU5hbWVcIiA6IG51bGwsXG4gIFwicm9sZU9mZmljZU5hbWVcIiA6IG51bGwsXG4gIFwicm9sZU9mZmljZUNvZGVcIiA6IG51bGwsXG4gIFwicm9sZU9mZmljZUlkXCIgOiBudWxsLFxuICBcIm9mZmljZUNvZGVcIiA6IFwiMTRcIixcbiAgXCJ1c2VyQ29va2llS2V5XCIgOiBcIjM3M19DVFRJQ1wiLFxuICBcImRhdGFBdXRob3JpdHlJZFwiIDogbnVsbCxcbiAgXCJwcmluY2lwYWxPcmdDb2RlXCIgOiBcIua3seWcs-a1t-S6i-WxgFwiLFxuICBcIm9mZmljZVR5cGVcIiA6IG51bGwsXG4gIFwibWFuYWdlVHlwZVwiIDogbnVsbCxcbiAgXCJsb2dpblRpbWVcIiA6IG51bGwsXG4gIFwic3lzdGVtUmF0ZVwiIDogbnVsbCxcbiAgXCJob21lQ3VycmVuY3lcIiA6IG51bGwsXG4gIFwib2ZmaWNlTWRtSWRcIiA6IG51bGwsXG4gIFwiY29ycE1kbUlkXCIgOiBudWxsLFxuICBcImNvcnBTaG9ydENvZGVcIiA6IG51bGwsXG4gIFwic3lzUm9sZUxpc3RcIiA6IG51bGwsXG4gIFwiZW5hYmxlZFwiIDogZmFsc2UsXG4gIFwiYXV0aG9yaXRpZXNcIiA6IFsgXSxcbiAgXCJhY2NvdW50Tm9uRXhwaXJlZFwiIDogZmFsc2UsXG4gIFwiYWNjb3VudE5vbkxvY2tlZFwiIDogZmFsc2UsXG4gIFwiY3JlZGVudGlhbHNOb25FeHBpcmVkXCIgOiBmYWxzZVxufSJ9.JFdvpVBuiz8HEoORkeuWybBTGfihu4wWyBYyFuSKQ1c',
    'msa_token': '4d8f42a8-4d7e-4e65-9f15-bd5b0a7d44d8'
}

#组装请求配置
session = requests.Session()
session.headers.update(headers)


#请求指标11船舶污染物的接收和处理情况备案数量pip install some_library
response_lo = session.get(BASE_URL11, cookies=cookies)

#响应回来的参数
print("响应回来的参数:",response_lo.text)
#响应回来的参数文本转json
strData = json.loads(response_lo.text)
strRecordsData = strData['data']['records'] #获取到具体数据的位置

#获取数据情况records、total、size、current、pages
print("获取："+str(strData['data']['total'])+"条数据","获取："+str(strData['data']['pages'])+"页数据","获取每页设置"+str(strData['data']['size'])+"条数据")

#打印每一列数据的字段key
print("获取建表字段：" , tuple(strRecordsData[0].keys())  )

if response_lo.status_code == 200:
    # 数据库连接配置
    db_config = {
        'host': '191.254.8.220',
        'user': 'root',
        'password': 'Szmsa@520',
        'db': 'spiders',
        'charset': 'utf8mb4',
        'cursorclass': pymysql.cursors.DictCursor
    }
    # 表名和字段名
    table_name = 'ywtg_ship_wuran_jieshou_chuli_beianshu_11'
    table_accepter = 'ywtg_ship_wuran_jieshou_chuli_beianshu_accepter_11'
    # 连接数据库
    connection = pymysql.connect(**db_config)

    try:
        # 开始事务
        with connection.cursor() as cursor:
            # 循环插入每条记录
            for record in strRecordsData:
                # 获取需要插入的字段名称
                fields = ', '.join(record.keys())
                # 判断是否包含 "index"，如果包含则替换为 "index1"
                if "index" in fields:
                    fields = fields.replace("index", "index1")
                # 拼装需要插入字段对应的%号个数
                values = ", ".join(['%s'] * len(record))
                # 判断是否包含 "None"，如果包含则替换为 "null"
                if "None" in values:
                    values = values.replace("None", "null")
                # print("!!!"+fields)
                # 查详情的windowApplyId
                windowApply_id = record['windowApplyId']
                #print("查详情的windowApplyId:",windowApply_id)

                # 构建插入语句，插入业务主表
                insert_stmt = (
                    "INSERT INTO `{table}` ({fields}) "
                    "VALUES ({values})"
                ).format(
                    table=table_name,
                    fields=fields,
                    values=values
                )
                #插入语句
                print("插入SQl语句：",insert_stmt)
                print("插入值：",tuple(record.values()))
                # 执行sql语句插入数据，record.values()用于获取字段中的值
                cursor.execute(insert_stmt, tuple(record.values()))
                # 获取申请单审核员信息


                params ={'windowApplyId': windowApply_id}
                response_auditor = session.get(GET_INFO_URL+ windowApply_id, cookies=cookies)
                auditorDataText = json.loads(response_auditor.text)
                #print("获取审查人详情:",auditorDataText['data']['acceptList'])

                # 如果审核人列表为空
                if auditorDataText['data']['acceptList'] is not None:
                    auditorList = auditorDataText['data']['acceptList']
                else:
                    auditorList = []  # 如果结构不符合预期，设置为空列表

                #获取的成功状态
                if response_auditor.status_code == 200:
                    for auditor in auditorList:
                        #获取需要插入的字段名称
                        auditor_fields = ', '.join(auditor.keys())
                        print("获取审查人表格字段:", auditor.keys())
                        # 拼装需要插入字段对应的%号个数
                        auditor_values = ", ".join(['%s'] * len(auditor))
                        insert_auditor = (
                            "INSERT INTO `{table}` ({fields}) "
                            "VALUES ({values})"
                        ).format(
                            table=table_accepter,
                            fields=auditor_fields,
                            values=auditor_values
                        )

                        cursor.execute(insert_auditor, tuple(auditor.values()))
        # 提交事务
        connection.commit()
    finally:
        # 关闭数据库连接
        connection.close()



